In this video you will learn how to import financial data from an excel file.
So in the whole upcoming section we will extensively work with the S&P 500 index which isn't easy read
a summary or an aggregation of the 500 largest listed U.S. companies or in other words that actually
aggregates or summarizes the performance of the 500 largest U.S. stocks.
So later on the cost will have a deeper look into a stock and it says.
But now for the time being we first need to import the S&P 500 price data from an excel file and this
works pretty much in the same way as importing data from a CSP file.
So instead of using the direct pan US method we'd see a sphere.
We use surprise the read Excel method.
Here we are in the Excel file and we can see S&P 500 price data starting from nineteen hundred seventy
and to wireless today and we can also see here we have a column open high low close adjusted close and
volume so this is a tabular data organization that we already know and that we also require here for
append US data frame and we can simply import the tabular data into append us and we can do this with
the read Excel method.
Now we are back here now to took a notebook and first of all we need to import pandas as speedy as always
and then we want to import our tabular data our S&P price data and we import the data from the file
as S&P 500 Excel s so this is the file name of our excel file.
And we simply have to pass to the file name to the direct Panda's method.
The read excel.
So let's do this and let's have a look here.
And this looks actually quite familiar.
So here we have the first column data starting in the year nineteen hundred seventy.
And then we have the columns open high low close adjusted close and volume so here we have our date
column and we all know that the best the data type would be a day time index and let's have a look here
with the info method on some meta information
and we can already see here in the date column that we have here the data type date time 64.
So this is actually quite comfortable and Penthouse tries to adopt the excel data types as much as possible.
So also an excel we have a date format.
And typically when we import data and to append us with Excel then pen US tries to identify and can
identify the data format in the Excel file and then us creates actually a daytime data type for the
date column.
So here we are back in the Excel file and already in the Excel file here the column date is actually
has the format the data in Excel and the pen does successfully actually adopt that the data type here.
So in this case there's actually no need to transform the data type of for the data column to a date
time.
But that's the functionality and the parameter to do so.
And to be on the safe side you can also pass here that date column to the parameter past dates.
So this works exactly in the same way as with the read the CSA year.
And we already know that that might make sense to have for the date columnist or the index of our data
frame by doing so we're creating a day time index and the consequently then we have a much higher functionality
with a day time index.
So also give us the read Excel method we have here the parameter index collar and TV pass date.
So let's have a look here
so now here we are on the left hand side to the day time index state with our time stamps.
And as I said before this example so there was no need to pass them the date column to the past states
per meter but to be on the safe side you should always use this here.
So sometimes that might be the case that penned US cannot recognize a date column in excel.
So we should always use service here and is actually another parameter in the read Excel method.
And this is called the Youth course.
And if we can define which columns we want to import from our excel file.
So let's go back to our excel file and we can see now excel file that we have data from the Excel columns.
A and to g inclusive and in the read Excel method we can actually define which of the columns.
We want to impart.
So now let's go back here to our Jupiter notebook and actually the default behavior is and that penned
US imports all columns.
So as you can see here if you didn't pass any information to the used class parameter and by default
pandas actually imports you all Excel columns.
So the default functionality is that we import all columns.
And in this case it's from a to G.
And actually we could explicitly pass here the columns a until G.
And we have to do this here within quotation marks as a string and actually both ends are here inclusive.
So by passing a total G to the youth cause parameter we are importing the columns a until G and both
ends are inclusive.
So let's throw this here and no surprise we get the same columns as here above.
So all columns but we could also say that we are not interested in the columns adjusted close and volume.
So let's go back to our excel file.
So we only want to import the columns from a until E without the columns F and G and we can do this
and instead of having from a until 2 year we pass here we want to f for the columns from a until e inclusive
and if we can see now that we have only the columns um date open high low and close from the Excel file
imported and actually the use cost parameters quite versatile.
So we can also pass here as a string.
The Excel column separated by a comma.
So for example we want to f a then b then we also want to F C D and E.
So let's have a look here so here we have the first five columns M date open high low close and of course
we can also say that we do not need uh the column B so we can just delete it here.
And we could also say that we want to have the column A and then the columns C tell e and we can do
this here in this way.
So this is no problem.
So this gives us here the same output.
And there's actually another parameter called the sheet name so let's go back to our excel file and
we can see here at the bottom that actually our excel file consists of two sheets.
So the very first sheet as of the S&P 500 sheet but we also have the sheet sales and we have some information
on the sides of the sets.
MAN MIKE Jim Stephen Joe and Tom.
So let's go back here to our first see it so by for the read Excel method actually imparts the very
first cheat and also zero based.
And next thing applies here.
So this is the first cheat at an exposition 0 and the Save sheet is set in exposition 1.
And let's assume that actually you want to import the same sheet and we can do this.
So that's an additional parameter sheet name.
And if we can pass as a string the sheet name for example S&P 500.
And alternatively we can also pass here the position of the sheet.
So we have seen already that the sheet S&P 500 is at index positions zero.
So this also works now let's try to import the safe sheet and typically pass either in position 1
or alternatively we can pass the sheet name.
Say it's
all right.
So we have decided now to import the S&P 500 data and we are only interested in the first column.
So we are actually not interested in adjusted close and volume therefore we use here of pass here a
to year to the youth course perimeter and then we save for the residing data frame and the variable
S P 500.
So let's do this here and let's have a look at the first five rows.
So these are the first five rows and we can see here that our dataset starts at the end of the year
nineteen hundred seventy year.
So this this year the thirty first of December then we have January to nineteen hundred seventy one
and actually we have fewer daily information.
So let's have a look at the last five rows
and here we have um the S&P price information to the very end of the year 2000 and 18.
So the last business day and the lot last time stamp of the year 2018 is uh the twenty eighth of December
to eighteen.
And it's also have a look here at some information with the info method so we FCA a day time index with
twelve thousand one hundred and seven entries and we have further four columns open high low and close
where we have actually a float data types.
So we have fair price information and actually now we can also export our data frame and create a new
CSP file with the method to see a sphere and we call our new CSP file as P 500 dot CSP and we could
also create a brand new excel file with them the method to excel and let's assume we name the new excel
file s p 500 reduced because we reduced our S&P 500 data set by the last two columns.
So the new excel file is now called S P 500 reduce dot Excel s so let's throw this here and consequently
if you have a look at our photo we have now two new five.
So once yes we file and one excel file.
So this was a video on imparting financial data from an excel file and starting with the next video
we are actually importing the S&P 500 data from the CSP file here.
So this is the the way how we will do it but of course we could also import the S&P 500 data from the
Excel file here.
All right we are finished now and hope to see you also in the next videos by.